package sgi.server;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Blob;

import com.sun.rowset.CachedRowSetImpl;

import sgi.message.MessageRetrieveTariff.TariffRow;
import sgi.details_new.*;
import sgi.message.*;
import sgi.misc.FloatDimension;
import sgi.misc.History;
import sgi.misc.Image;
import ocsf.server.*;

public class SGIServer extends AbstractServer {
	  /**
	   * The default port to listen on.
	   */
	  final public static int DEFAULT_PORT = 5555;
	  private Connection connDB;
	  private SGIServerUI serverUI;
	  private String dbName,user,pass;
	  
	  public SGIServer(SGIServerUI serverUI)
	  {
		  super();
		  this.serverUI = serverUI;
		  serverUI.setVisible(true);
	  }
	 /** Creates connection to database */
	  public void initDBConnection(String dbName,String user, String pass) throws Exception
	  {
		  try 
			{
	            Class.forName("com.mysql.jdbc.Driver").newInstance();
	            connDB = DriverManager.getConnection("jdbc:mysql://93.172.134.98/"+dbName, user, pass);
	            setUser(user);
	            setPass(pass);
	            setdbName(dbName);
	        } catch (Exception ex) {throw ex;}
	  }
	  
	  /** handles reaction of server to messages from client */
	  @SuppressWarnings({ "deprecation", "resource" })
	public void handleMessageFromClient
	    (Object msg, ConnectionToClient client)
	  {
			try 
			{
				Statement stmt = connDB.createStatement();
				/** message from client is request for login */
							if(msg instanceof MessageLogin) 
							{
								ResultSet rs = stmt.executeQuery(((Message)msg).getQuery());
								rs.next();
						/**Check if correct user details*/	
								if((rs.getString(1)).compareTo("1") == 0) /**  */
								{
						/**Set userOnline status to valid*/			
									stmt.executeUpdate("update users set userOnline='1' where username = '" + ((MessageLogin)msg).getUsername() + "' AND password = '" + ((MessageLogin)msg).getPassword() + "'");
									((MessageLogin)msg).setLoginSucceeded(true);
						/**Get privilege level*/
									rs = stmt.executeQuery("select privilege from users where username = '" + ((MessageLogin)msg).getUsername() + "' AND password = '" + ((MessageLogin)msg).getPassword() + "'");
									rs.next();
									((MessageLogin)msg).setPrivilege(Integer.valueOf(rs.getString(1)));
						/**Return message to client*/			
									client.sendToClient(msg);
									serverUI.display(((MessageLogin)msg).getUsername() + " logged in");
								}
								else /** login failed */
								{
									((MessageLogin)msg).setLoginSucceeded(false);
									((MessageLogin)msg).setLoginFailedMessage("Login failed");
									client.sendToClient(msg);
									serverUI.display(((MessageLogin)msg).getUsername() + "-login failed.");
								}
								
						/** Push messages about submitted complaints */		
								List<MessageNotRespondDetails> results = new ArrayList<MessageNotRespondDetails>();
								MessageNotRespondDetails cmd;
								SimpleDateFormat df1 = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss.0");
								String dateformat;
								Calendar cal1 = Calendar.getInstance();
								cal1.add(Calendar.DAY_OF_MONTH, -1);
								dateformat= df1.format(cal1.getTime());
								
								rs = stmt.executeQuery("select * from complaint where pending =0 and datetime<'"+ dateformat+"'  ");
								if(rs.next() == true)
								{
									
									do{
										cmd = new MessageNotRespondDetails(rs.getString(1),rs.getString(2));
										results.add(cmd);
									}while(rs.next());
								}
								String title;
								String username;
								String DateTime;
								for (int i=0 ; i< results.size() ; i++)
								{
									username = results.get(i).getUserName();
									DateTime = results.get(i).getDateTime();
									rs = stmt.executeQuery("select title from complaint where username='" + username + "' and datetime ='"+DateTime+"'");
									rs.next();
									title = rs.getString(1);
									stmt.executeUpdate("insert into messages  values ('"+ username + "','Complaint "+ title + "','Complaint unresolved in 24 hours.')");
								}
								
								rs.close();
							}
				/** Message is request for log off. sets user online status to FALSE */
						else if(msg instanceof MessageLogoff) 
						{
							stmt.executeUpdate("update users set userOnline='0' where username = '" + ((MessageLogoff)msg).getUsername() +  "' AND password = '" + ((MessageLogoff)msg).getPassword() + "'");
						}
				/** Deprecated */
						else if(msg instanceof MessagePrompt)
						{	
							serverUI.display(((MessagePrompt)msg).getQuery());
							
						}
				/** message is registration form. server translates it into query and adds to database */			
						else if(msg instanceof MessageRegister)
						{
							/**Execute query to check if username already exists*/
							ResultSet rs = stmt.executeQuery("select COUNT(*) from users where username = '"+((MessageRegister)msg).getUsername()+"'");
							rs.next();
							if((rs.getString(1)).compareTo("1") == 0) /** if user already exists, inform sender of request */
							{
								((MessageRegister)msg).setStatus(false);
								((MessageRegister)msg).setNotif("Username " + ((MessageRegister)msg).getUsername() + " already exists.");
							}
							else /** if user doesn't yet exist, add to Customers table */
							{
								stmt.executeUpdate(((MessageRegister)msg).getQuery());
								stmt.executeUpdate(((MessageRegister)msg).getQcustomer());
								((MessageRegister)msg).setStatus(true);
								((MessageRegister)msg).setNotif("New customer created");
								serverUI.display("Customer "+((MessageRegister)msg).getUsername() + " created.");
							}
							rs.close();
							client.sendToClient(msg);	
						}
				/** handle complaint message from client */
						else if(msg instanceof MessageComplaint)
						{
							stmt.executeUpdate(((MessageComplaint)msg).getQuery());
							((MessageComplaint)msg).setReceived(true);
							 Date date = new Date();
							 stmt.executeUpdate("update complaint set datetime = '"+(((int)date.getYear()+1900))+"-"+(((int)date.getMonth()+1))+"-"+((int)date.getDate())+" "+date.getHours()+":"+date.getMinutes()+":"+date.getSeconds()+"' where datetime='0000-00-00 00:00:00'");
							serverUI.display("Customer "+((MessageComplaint)msg).getUsername() + " filed a complaint.");
							client.sendToClient(msg);
						}
				/** handle Search from client. Used in both Search and the Interactive Map.
				 *  Searches for images in the specified location and between the specified dates 
				 *  (for map: uses arbitrarily large range of dates) */
						else if(msg instanceof MessageSearch)
						{
							ResultSet rs = stmt.executeQuery(((MessageSearch)msg).getQuery());
							if(rs.next() == false)
								((MessageSearch)msg).setImages(null);
							else
							{
								((MessageSearch)msg).setImages(new ArrayList<ImageDetails>());
								do {
									((MessageSearch)msg).getImages().add(new ImageDetails(Integer.valueOf(rs.getString(1)),rs.getString(2),rs.getString(3),new FloatDimension(Float.valueOf(rs.getString(4)),Float.valueOf(rs.getString(5)))));
								} while (rs.next());
							}						
							/**update search history*/
							stmt.executeUpdate(((MessageSearch)msg).getqHistory());
							client.sendToClient(msg);
							serverUI.display("Search completed.");
							rs.close();
						}
				/** returns list of pending complaints from database to client */
						else if(msg instanceof MessageGetComplaint)	
						{
							SimpleDateFormat df1 = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss.0");
							String dateformat;
							Calendar cal1 = Calendar.getInstance();
							cal1.add(Calendar.DAY_OF_MONTH, -1);
							dateformat= df1.format(cal1.getTime());
							ResultSet rs = stmt.executeQuery("select * from complaint where pending =0 and datetime>='"+ dateformat+"'  ");
							if(rs.next() == false)
								((MessageGetComplaint)msg).setComplaints(null);
							else
							{
								((MessageGetComplaint)msg).setComplaints(new ArrayList<ComplaintDetails>());
								do{
									((MessageGetComplaint)msg).getComplaints().add(new ComplaintDetails(rs.getString(1),rs.getString(2),Integer.valueOf(rs.getString(3)),rs.getString(4),rs.getString(5)));
								} while (rs.next());
								client.sendToClient(msg);
							}
							rs.close();
						}
				/** updates customer's balance and status of customer's complaint (complaint approved) */
						else if(msg instanceof MessageCompensation)
						{
							ResultSet rs = stmt.executeQuery(((MessageCompensation)msg).getQuery());
							if(rs.next())
							{
								String compensation = String.valueOf(((MessageCompensation)msg).getCompensation());
								stmt.executeUpdate("update customers set balance=" + (Float.valueOf(rs.getString(1))+((MessageCompensation)msg).getCompensation()) + " where username='"+((MessageCompensation)msg).getUsername()+"'");
								rs = stmt.executeQuery("select title from complaint where username='" + ((MessageCompensation)msg).getUsername() + "' and datetime ='"+((MessageCompensation)msg).getDatetime()+"'");
								rs.next();
								String title = rs.getString(1);
								stmt.executeUpdate("update complaint set pending=1, approved=1 where username='" + ((MessageCompensation)msg).getUsername() + "' and datetime ='"+((MessageCompensation)msg).getDatetime()+"'");
								stmt.executeUpdate("insert into messages  values ('"+((MessageCompensation)msg).getUsername() + "','Complaint "+ title + "','Complaint accepted and recieved  "+compensation + " compensation.')");
								serverUI.display(((MessageCompensation)msg).getUsername() + " complaint received.");
								client.sendToClient(msg);
							}
							rs.close();
						}
				/** updates status of customer's complaint and sends message to client (complaint declined) */
						else if (msg instanceof MessageDeclineComplaint)
						{
								stmt.executeUpdate(((MessageDeclineComplaint)msg).getQuery());
								ResultSet rs = stmt.executeQuery("select title from complaint where username='" + ((MessageDeclineComplaint)msg).getUsername() + "' and datetime ='"+((MessageDeclineComplaint)msg).getDatetime()+"'");
								rs.next();
								String title = rs.getString(1);
								stmt.executeUpdate("insert into messages  values ('"+((MessageDeclineComplaint)msg).getUsername() + "','Complaint "+ title + "','Complaint declined, no compensation.')");
								serverUI.display(((MessageDeclineComplaint)msg).getUsername() + " complaint received.");
								client.sendToClient(msg);
						}
				/** return user account information */
						else if (msg instanceof MessageViewInfo)
						{
							String DataLayer;
							String DateTime;
							String ImagePack;
							ResultSet rs = stmt.executeQuery("select COUNT(*) from data_layer_subscription where username = '"+ ((MessageViewInfo)msg).getUserName() + "' ");
							rs.next();
							if(rs.getInt(1)==1)
							{
								DataLayer = "Yes";
							}
							else
								DataLayer = "No";
							
							rs = stmt.executeQuery("select COUNT(*) from date_time_subscription where username = '"+ ((MessageViewInfo)msg).getUserName() + "' ");
							rs.next();
							if(rs.getInt(1)==1)
							{
								DateTime = "Yes";
							}
							else
								DateTime = "No";
							
							rs = stmt.executeQuery("select COUNT(*) from image_pack_subscription where username = '"+ ((MessageViewInfo)msg).getUserName() + "' ");
							rs.next();
							if(rs.getInt(1)==1)
							{
								ImagePack = "Yes";
							}
							else
								ImagePack = "No";
							
							rs = stmt.executeQuery(((MessageViewInfo)msg).getQuery());
							if(rs.next())
							{
							((MessageViewInfo)msg).setAccountInfo(new ArrayList<AccountInfoDetails>());
								((MessageViewInfo)msg).getAccountInfo().add(new AccountInfoDetails(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),Float.valueOf(rs.getString(6)),DataLayer,DateTime,ImagePack));
							} 
							else
							{
								((MessageViewInfo)msg).setAccountInfo(null);
							}
						client.sendToClient(msg);
						serverUI.display("Account info search completed.");
						}				
				/** message is request for purchase of sample image */
						else if(msg instanceof MessagePurchaseSample)
						{
							/**check if user already purchased this sample*/
							ResultSet rs = stmt.executeQuery(((MessagePurchaseSample)msg).getqCheckAlreadyPurchasedSample());
							rs.next();
							if(rs.getInt(1) == 0) 
							{					
								/**Check if customer already purchased image*/
								rs = stmt.executeQuery(((MessagePurchaseSample)msg).getqCheckAlreadyPurchasedImage());
								rs.next();
								if(rs.getInt(1) == 0)
								{
									
									/**Get sample tariff*/
									rs = stmt.executeQuery(((MessagePurchaseSample)msg).getQuery());
									rs.next();
									
									/**Save the tariff*/
									((MessagePurchaseSample)msg).setTariff(rs.getFloat(1));
									((MessagePurchaseSample)msg).setPrice(((MessagePurchaseSample)msg).getTariff());
								
									/**Get customers balance*/
									rs = stmt.executeQuery(((MessagePurchaseSample)msg).getqGetBalance());
									rs.next();
									int balance = rs.getInt(1);
								
									
							
									/**check if balance is sufficient to purchase sample*/
									if((balance-((MessagePurchaseSample)msg).getPrice()) >= 0)
									{
	
										/**mark purchase as completed*/
										((MessagePurchaseSample)msg).setPurcahseComplete(true);
										((MessagePurchaseSample)msg).setSampleAlreadyPurchased(false);
										((MessagePurchaseSample)msg).setImageAlreadyPurchased(false);
										
										/**update balance*/
										stmt.executeUpdate("update customers set balance = " + String.valueOf(balance-((MessagePurchaseSample)msg).getPrice()) + " where username = '" + ((MessagePurchaseSample)msg).getUsername() + "'");
								
										/**retrieve the sample from server*/
										rs = stmt.executeQuery( "select sample from images where imageid =" + ((MessagePurchaseSample)msg).getImageid() );
								
										/**insert the image into the message*/
										rs.next();
										Blob blob = rs.getBlob(1);
										((MessagePurchaseSample)msg).setBlobAsBytes(blob.getBytes(1,(int)blob.length()));
						
										/**update in user_images table*/
										stmt.executeUpdate("insert into user_images values (" +	((MessagePurchaseSample)msg).getImageid() +",'"+((MessagePurchaseSample)msg).getUsername()+"',0,1,0,0)");
										
										/**updates samples purchased in statistics table*/
										this.UpdateDailyStats(stmt, "purchased_samples=purchased_samples+1");
										
										/**update history*/
										stmt.executeUpdate("INSERT INTO `sgi`.`history_purchase` (`username`, `description`) VALUES ('"+((MessagePurchaseSample)msg).getUsername()+"', 'Sample-ImageID-"+((MessagePurchaseSample)msg).getImageid()+"-Price-"+((MessagePurchaseSample)msg).getTariff() +"');");

									}
									else
									{
										((MessagePurchaseSample)msg).setSampleAlreadyPurchased(true);
										((MessagePurchaseSample)msg).setPurcahseComplete(false);
									}
								}
								else
								{
									((MessagePurchaseSample)msg).setImageAlreadyPurchased(true);
									((MessagePurchaseSample)msg).setPurcahseComplete(false);
								}
		
										
							try {
								client.sendToClient(msg);
						} catch (IOException e) { e.printStackTrace(); }
							rs.close();
							}}
				/** message is request for purchase of image from database */
						else if(msg instanceof MessagePurchaseImage)
						{
							/**check if user already purchased this image*/
							ResultSet rs = stmt.executeQuery(((MessagePurchaseImage)msg).getqCheckAlreadyPurchasedImage());
							rs.next();
							if(rs.getInt(1) == 0)
							{					
								/** Get image tariff*/
								rs = stmt.executeQuery(((MessagePurchaseImage)msg).getQuery());
								rs.next();
								/**Save the tariff*/
								((MessagePurchaseImage)msg).setTariff(rs.getFloat(1));
								((MessagePurchaseImage)msg).setPrice(((MessagePurchaseImage)msg).getTariff());
								
								/**Get customers balance*/
								rs = stmt.executeQuery(((MessagePurchaseImage)msg).getqGetBalance());
								rs.next();
								int balance = rs.getInt(1);
	
								/**Check if customer already purchased image*/
								rs = stmt.executeQuery(((MessagePurchaseImage)msg).getqCheckAlreadyPurchasedSample());
								rs.next();
								if(rs.getInt(1) == 1)
								{
									/**If sample already purchased, discount sample price*/
									((MessagePurchaseImage)msg).setPrice((float)(((MessagePurchaseImage)msg).getPrice()-((MessagePurchaseImage)msg).getTariff()*0.04));
									((MessagePurchaseImage)msg).setSampleAlreadyPurchased(true);
								}
								else
									((MessagePurchaseImage)msg).setSampleAlreadyPurchased(false);
								/**Check if customer has image pack subscription*/
								rs = stmt.executeQuery(((MessagePurchaseImage)msg).getqCheckImagePackSubscription());
								rs.next();
								/**if has image pack subscription decrement image counter and give discount*/
								if(rs.getInt(1) == 1)
								{
									stmt.executeUpdate(((MessagePurchaseImage)msg).getqDecrementImagePackSubscription());
									((MessagePurchaseImage)msg).setPrice((float)(((MessagePurchaseImage)msg).getPrice()*0.7));
								}
								
								/**check if balance is sufficient to purchase image*/
								if((balance-((MessagePurchaseImage)msg).getPrice()) >= 0)
								{
	
									/**mark purchase as completed*/
									((MessagePurchaseImage)msg).setPurcahseComplete(true);
									/**update balance*/
									stmt.executeUpdate("update customers set balance = " + String.valueOf(balance-((MessagePurchaseImage)msg).getPrice()) + " where username = '" + ((MessagePurchaseImage)msg).getUsername() + "'");									
						
									/**Check data layer subscriptions*/
									rs = stmt.executeQuery(((MessagePurchaseImage)msg).getqCheckDataLayerSubscription());
									if(rs.next() == true)
									{
										if((rs.getInt(1) == 1) && (rs.getInt(2)==1))
										{
											((MessagePurchaseImage)msg).setHasCommerceSubscription(true);
											((MessagePurchaseImage)msg).setHasInfrastractureSubscription(true);
											/**retrieve the images from server*/
											rs = stmt.executeQuery( "select image1,image2,image3,image4 from images where imageid =" + ((MessagePurchaseImage)msg).getImageid() );
											/**insert the images into the message*/
											rs.next();
											Blob blob = rs.getBlob(1);
											((MessagePurchaseImage)msg).setBlobAsBytes(blob.getBytes(1,(int)blob.length()));
											blob = rs.getBlob(2);
											((MessagePurchaseImage)msg).setBlobAsBytesInfrastracture(blob.getBytes(1,(int)blob.length()));
											blob = rs.getBlob(3);
											((MessagePurchaseImage)msg).setBlobAsBytesCommerce(blob.getBytes(1,(int)blob.length()));
											blob = rs.getBlob(4);
											((MessagePurchaseImage)msg).setBlobAsBytesCommerceInfrastracture(blob.getBytes(1,(int)blob.length()));
										
											/**update in user_images table*/
											if(((MessagePurchaseImage)msg).isSampleAlreadyPurchased() == true)
												stmt.executeUpdate("update user_images set image=1,commerce=1,infrastracture=1 where username = '" + ((MessagePurchaseImage)msg).getUsername() + "' and imageid = " + ((MessagePurchaseImage)msg).getImageid() );
											else
												stmt.executeUpdate("insert into user_images values (" +	((MessagePurchaseImage)msg).getImageid() +",'"+((MessagePurchaseImage)msg).getUsername()+"',1,0,1,1)");

											}
										else if(rs.getInt(1) == 1)
										{
											((MessagePurchaseImage)msg).setHasInfrastractureSubscription(true);
											((MessagePurchaseImage)msg).setHasCommerceSubscription(false);
											/**retrieve the images from server*/
											rs = stmt.executeQuery("select image1,image2 from images where imageid =" + ((MessagePurchaseImage)msg).getImageid() );
											rs.next();
											Blob blob = rs.getBlob(1);
											((MessagePurchaseImage)msg).setBlobAsBytes(blob.getBytes(1,(int)blob.length()));
											blob = rs.getBlob(2);
											((MessagePurchaseImage)msg).setBlobAsBytesInfrastracture(blob.getBytes(1,(int)blob.length()));
											
											/**update in user_images table*/
											if(((MessagePurchaseImage)msg).isSampleAlreadyPurchased() == true)
												stmt.executeUpdate("update user_images set image=1,infrastracture=1 where username = '" + ((MessagePurchaseImage)msg).getUsername() + "' and imageid = " + ((MessagePurchaseImage)msg).getImageid() );
											else
												stmt.executeUpdate("insert into user_images values (" +	((MessagePurchaseImage)msg).getImageid() +",'"+((MessagePurchaseImage)msg).getUsername()+"',1,0,0,1)");

										}
										else
										{
											((MessagePurchaseImage)msg).setHasCommerceSubscription(true);
											((MessagePurchaseImage)msg).setHasInfrastractureSubscription(false);
											/**retrieve the images from server*/
											rs = stmt.executeQuery( "select image1,image3 from images where imageid =" + ((MessagePurchaseImage)msg).getImageid() );
											rs.next();
											Blob blob = rs.getBlob(1);
											((MessagePurchaseImage)msg).setBlobAsBytes(blob.getBytes(1,(int)blob.length()));
											blob = rs.getBlob(2);
											((MessagePurchaseImage)msg).setBlobAsBytesCommerce(blob.getBytes(1,(int)blob.length()));
											
											/**update in user_images table*/
											if(((MessagePurchaseImage)msg).isSampleAlreadyPurchased() == true)
												stmt.executeUpdate("update user_images set image=1,Commerce=1 where username = '" + ((MessagePurchaseImage)msg).getUsername() + "' and imageid = " + ((MessagePurchaseImage)msg).getImageid() );
											else
												stmt.executeUpdate("insert into user_images values (" +	((MessagePurchaseImage)msg).getImageid() +",'"+((MessagePurchaseImage)msg).getUsername()+"',1,0,1,0)");

										}
											
									}
									else
									{
										((MessagePurchaseImage)msg).setHasCommerceSubscription(false);
										((MessagePurchaseImage)msg).setHasInfrastractureSubscription(false);
										/**retrieve the image from server*/
										rs = stmt.executeQuery( "select image1 from images where imageid =" + ((MessagePurchaseImage)msg).getImageid() );
										/**insert the image into the message*/
										rs.next();
										Blob blob = rs.getBlob(1);
										((MessagePurchaseImage)msg).setBlobAsBytes(blob.getBytes(1,(int)blob.length()));
										
										/**update in user_images table*/
										if(((MessagePurchaseImage)msg).isSampleAlreadyPurchased() == true)
											stmt.executeUpdate("update user_images set image=1 where username = '" + ((MessagePurchaseImage)msg).getUsername() + "' and imageid = " + ((MessagePurchaseImage)msg).getImageid() );
										else
											stmt.executeUpdate("insert into user_images values (" +	((MessagePurchaseImage)msg).getImageid() +",'"+((MessagePurchaseImage)msg).getUsername()+"',1,0,0,0)");
									}
									
									/**update history*/
									stmt.executeUpdate("INSERT INTO `sgi`.`history_purchase` (`username`, `description`) VALUES ('"+((MessagePurchaseImage)msg).getUsername()+"', 'Image-ImageID-"+((MessagePurchaseImage)msg).getImageid()+"-Price-"+((MessagePurchaseImage)msg).getTariff() +"');");

									/**updates images purchased in statistics table*/
									this.UpdateDailyStats(stmt, "purchased_images=purchased_images+1");
								}
								else
								{
									/**mark purchase as failed*/
									((MessagePurchaseImage)msg).setPurcahseComplete(false);
									((MessagePurchaseImage)msg).setInsufficiantBalance(true);
									((MessagePurchaseImage)msg).setImageAlreadyPurchased(false);
								}

							}
							else
							{
								/**mark purchase as failed*/
								((MessagePurchaseImage)msg).setPurcahseComplete(false);
								((MessagePurchaseImage)msg).setImageAlreadyPurchased(true);
								((MessagePurchaseImage)msg).setInsufficiantBalance(false);
							}
							try {
								client.sendToClient(msg);
						} catch (IOException e) { e.printStackTrace(); }
							rs.close();
						}
				/** message is request for data layers subscription */
						else if(msg instanceof MessageDataLayer)
						{
								int datalayer_id,datetime_id,imagepack_id;
								stmt.executeQuery(((MessageDataLayer)msg).getQuery());
								
								ResultSet rs = stmt.executeQuery(((MessageDataLayer)msg).getqCheckhasSubscription());
								rs.next();
								if(rs.getInt(1) == 1)
								{
									((MessageDataLayer)msg).setValid(false);
									((MessageDataLayer)msg).setNotif("Subscriber already exists!");			
								}
								else
								{
									rs = stmt.executeQuery(((MessageDataLayer)msg).getqCountSubscriptions());
									rs.next();
									datalayer_id = rs.getInt(1);
									
									rs= stmt.executeQuery("select COUNT(subscription_id) from date_time_subscription");
									rs.next();
									datetime_id = rs.getInt(1);
									
									rs= stmt.executeQuery("select COUNT(subscription_id) from image_pack_subscription");
									rs.next();
									imagepack_id = rs.getInt(1);
									
									stmt.executeUpdate("insert into data_layer_subscription values ('"+ String.valueOf(datalayer_id+datetime_id+imagepack_id+1) +"','"+(((MessageDataLayer)msg).getUserName())+"','"+(((MessageDataLayer)msg).getCommerce())+"','"+(((MessageDataLayer)msg).getInfrastracture())+"',0)");
									((MessageDataLayer)msg).setValid(true);
									((MessageDataLayer)msg).setNotif("New DataLayer Subscription request created");
									serverUI.display("Subscriber request of "+((MessageDataLayer)msg).getUserName() + " created.");
									
									/**update history*/
									stmt.executeUpdate(((MessageDataLayer)msg).getqHistory());
									
									client.sendToClient(msg);
								}
								rs.close();
						}
				/**message is request for date/time subscription */ 
						else if(msg instanceof MessageDateTime)
						{
								Date date = new Date();
								int datalayer_id,datetime_id,imagepack_id;
								stmt.executeQuery(((MessageDateTime)msg).getQuery());	
								ResultSet rs = stmt.executeQuery(((MessageDateTime)msg).getqCheckHasSubscription());
								rs.next();
								if(rs.getInt(1) == 1)
								{
									((MessageDateTime)msg).setValid(false);
									((MessageDateTime)msg).setNotif("Subscriber already exists!");
								}
								else
								{
									rs = stmt.executeQuery("select COUNT(subscription_id) from data_layer_subscription");
									rs.next();
									datalayer_id = rs.getInt(1);
									
									rs= stmt.executeQuery(((MessageDateTime)msg).getqCountSubscriptions());
									rs.next();
									datetime_id = rs.getInt(1);
									
									rs= stmt.executeQuery("select COUNT(subscription_id) from image_pack_subscription");
									rs.next();
									imagepack_id = rs.getInt(1);
									
									stmt.executeUpdate("insert into date_time_subscription values ('"+ String.valueOf(datalayer_id+datetime_id+imagepack_id+1) +"','"+((MessageDateTime)msg).getUserName()+"','"+((MessageDateTime)msg).getLocation()+"','"+(((int)date.getYear()+1900))+"-"+(((int)date.getMonth()+1))+"-"+((int)date.getDate())+" "+date.getHours()+":"+date.getMinutes()+":"+date.getSeconds()+"','08:00:00',0)");
									((MessageDateTime)msg).setValid(true);
									((MessageDateTime)msg).setNotif("New DateTime Subscription request created");
									
									/**update history*/
									stmt.executeUpdate(((MessageDateTime)msg).getqHistory());
									
									serverUI.display("Subscriber request of "+((MessageDateTime)msg).getUserName() + " created.");
								}
								client.sendToClient(msg);
								rs.close();
						}
				/** message is request for image pack subscription */
						else if(msg instanceof MessageImagePack)
						{
								int datalayer_id,datetime_id,imagepack_id;
								stmt.executeQuery(((MessageImagePack)msg).getQuery());
								ResultSet rs = stmt.executeQuery(((MessageImagePack)msg).getqCheckHasSubscription());
								rs.next();
								if(rs.getInt(1) == 1)
								{
									((MessageImagePack)msg).setValid(false);
									((MessageImagePack)msg).setNotif("Subscriber already exists!");			
								}
								else
								{
									rs = stmt.executeQuery("select COUNT(subscription_id) from data_layer_subscription");
									rs.next();
									datalayer_id = rs.getInt(1);
									
									rs= stmt.executeQuery("select COUNT(subscription_id) from date_time_subscription");
									rs.next();
									datetime_id = rs.getInt(1);
									
									rs= stmt.executeQuery(((MessageImagePack)msg).getqCountSubscriptions());
									rs.next();
									imagepack_id = rs.getInt(1);
									
									stmt.executeUpdate("insert into image_pack_subscription values ('"+ String.valueOf(datalayer_id+datetime_id+imagepack_id+1) +"','"+((MessageImagePack)msg).getUserName()+"','"+ 200 +"',0)");
									((MessageImagePack)msg).setValid(true);
									((MessageImagePack)msg).setNotif("New ImagePack Subscription request created");
									serverUI.display("Subscriber request of "+((MessageImagePack)msg).getUserName() + " created.");
									
									/**update history*/
									stmt.executeUpdate(((MessageImagePack)msg).getqHistory());
									
									client.sendToClient(msg);
								}
								rs.close();
						}
							
				/** message is an image file with location/date details and data layers. Server uploads it to database and updates tables */
						else if(msg instanceof MessageImageAdd)
						{
							ResultSet rs = stmt.executeQuery(((MessageImageAdd)msg).getCountImageID());
							rs.next();
							Blob blob1 = new javax.sql.rowset.serial.SerialBlob(((MessageImageAdd)msg).getOriginalImage1());
							Blob blob2 = new javax.sql.rowset.serial.SerialBlob(((MessageImageAdd)msg).getOriginalImage2());
							Blob blob3 = new javax.sql.rowset.serial.SerialBlob(((MessageImageAdd)msg).getOriginalImage3());
							Blob blob4 = new javax.sql.rowset.serial.SerialBlob(((MessageImageAdd)msg).getOriginalImage4());
							Blob blob5 = new javax.sql.rowset.serial.SerialBlob(((MessageImageAdd)msg).getOriginalImage5());
							
							Connection conn = DriverManager.getConnection("jdbc:mysql://109.186.119.118/"+getdbName(), getUser(), getPass());
							String sql = "insert into images values (?,?,?,?,?,?,?,?,?,?)";
							java.sql.PreparedStatement prepstmt = conn.prepareStatement(sql);
							prepstmt.setInt(1, rs.getInt(1)+1);
							prepstmt.setString(2, ((MessageImageAdd)msg).getDate());
							prepstmt.setString(3, ((MessageImageAdd)msg).getLocation());
							prepstmt.setString(4, ((MessageImageAdd)msg).getLatitude());
							prepstmt.setString(5, ((MessageImageAdd)msg).getLongitude());
							
						    prepstmt.setObject(6,((MessageImageAdd)msg).getOriginalImage1());
						    prepstmt.setObject(7,((MessageImageAdd)msg).getOriginalImage2());
						    prepstmt.setObject(8,((MessageImageAdd)msg).getOriginalImage3());
						    prepstmt.setObject(9,((MessageImageAdd)msg).getOriginalImage4());
						    prepstmt.setObject(10,((MessageImageAdd)msg).getOriginalImage5());
						    
						    prepstmt.execute();
						    
						    /**updates daily new images and total amount of images in database, in statistics table*/
							this.UpdateDailyStats(stmt, "new_images=new_images+1,archive_size=archive_size+1");
							
							serverUI.display("Image has been uploaded to database.");
						}
				/** message is request for data layer subscription. returns data layer to client */
						else if (msg instanceof MessageGetDataLayer)
						{
							ResultSet rs = stmt.executeQuery(((MessageGetDataLayer)msg).getQuery());
							if(rs.next() == false)
								((MessageGetDataLayer)msg).setDataLayer(null);
							else
							{
								((MessageGetDataLayer)msg).setDataLayer(new ArrayList<SubscribeDetails>());
								do{
									((MessageGetDataLayer)msg).getDataLayer().add(new SubscribeDetails(rs.getString(1),"DataLayer"));
								} while (rs.next());
								client.sendToClient(msg);
							}
							rs.close();
						}
				/** message is request for date/time subscription. returns date/time subscription to client */
						else if (msg instanceof MessageGetDateTime)
						{
							ResultSet rs = stmt.executeQuery(((MessageGetDateTime)msg).getQuery());
							if(rs.next() == false)
								((MessageGetDateTime)msg).setDateTime(null);
							else
							{
								((MessageGetDateTime)msg).setDateTime(new ArrayList<SubscribeDetails>());
								do{
									((MessageGetDateTime)msg).getDateTime().add(new SubscribeDetails(rs.getString(1),"DateTime"));
								} while (rs.next());
								client.sendToClient(msg);
							}
							rs.close();
						}
				/** message is request for image pack subscription. returns image pack subscription to client */
						else if (msg instanceof MessageGetImagePack)
						{
							ResultSet rs = stmt.executeQuery(((MessageGetImagePack)msg).getQuery());
							if(rs.next() == false)
								((MessageGetImagePack)msg).setImagePack(null);
							else
							{
								((MessageGetImagePack)msg).setImagePack(new ArrayList<SubscribeDetails>());
								do{
									((MessageGetImagePack)msg).getImagePack().add(new SubscribeDetails(rs.getString(1),"ImagePack"));
								} while (rs.next());
								client.sendToClient(msg);
							}
							rs.close();
						}
				/**message by customer service representative, accept/decline data layer subscription purchase 
				 * depending on customer's credit balance*/
						else if (msg instanceof MessageAcceptDataLayer)
						{
							ResultSet rs = stmt.executeQuery(((MessageAcceptDataLayer)msg).getQuery());
							rs.next();
							Float balance = Float.valueOf(rs.getString(1));
							rs = stmt.executeQuery (((MessageAcceptDataLayer)msg).getQcheckDataLayers());
							
								if(rs.next())
								{
									if(balance>100.0)
									{
										stmt.executeUpdate("update customers set balance=" + (balance-100.0) + " where username='"+((MessageAcceptDataLayer)msg).getUserName()+"'");	
										stmt.executeUpdate("update data_layer_subscription set pending=1 where username='" + ((MessageAcceptDataLayer)msg).getUserName() + "'");
										serverUI.display(((MessageAcceptDataLayer)msg).getUserName() + "2 Data Layer Subscription approved.");
										/**updates # of datalayer subs in statistics table*/
										this.UpdateDailyStats(stmt, "datalayer_subs=datalayer_subs+1");
									}
									else
									{
										serverUI.display(((MessageAcceptDataLayer)msg).getUserName() + " not enough balance for both data layer purchase");
									}
								}
								else
								{
									if(balance>50.0)
									{
										stmt.executeUpdate("update customers set balance=" + (balance-50.0) + " where username='"+((MessageAcceptDataLayer)msg).getUserName()+"'");	
										stmt.executeUpdate("update data_layer_subscription set pending=1 where username='" + ((MessageAcceptDataLayer)msg).getUserName() + "'");
										serverUI.display(((MessageAcceptDataLayer)msg).getUserName() + "1 Data Layer subscription approved.");
										/**updates # of datalayer subs in statistics table*/
										this.UpdateDailyStats(stmt, "datalayer_subs=datalayer_subs+1");
									}
									else
									{
										serverUI.display(((MessageAcceptDataLayer)msg).getUserName() + " not enough balance for one data layer purchase");
									}
								}
								client.sendToClient(msg);
							rs.close();
						}
				/**message by customer service representative, accept/decline date/time subscription purchase 
				* depending on customer's credit balance*/	
						else if (msg instanceof MessageAcceptDateTime)
						{
							stmt.executeUpdate(((MessageAcceptDateTime)msg).getQuery());
							/**updates # of datalayer subs in statistics table*/
							this.UpdateDailyStats(stmt, "datetime_subs=datetime_subs+1");
							serverUI.display(((MessageAcceptDateTime)msg).getUserName() + "Date Time subscription approved.");
						}
				/**message by customer service representative, accept/decline image pack subscription purchase 
				 * depending on customer's credit balance*/
						else if (msg instanceof MessageAcceptImagePack)
						{
							stmt.executeUpdate(((MessageAcceptImagePack)msg).getQuery());
							/**updates # of datalayer subs in statistics table*/
							this.UpdateDailyStats(stmt, "imagepack_subs=imagepack_subs+1");
							serverUI.display(((MessageAcceptImagePack)msg).getUserName() + "Image Pack subscription approved.");
						}
				/**returns list of messages to client*/
						else if(msg instanceof MessageViewMessages)
						{
							ResultSet rs = stmt.executeQuery(((MessageViewMessages)msg).getQuery());
							if(rs.next() == false)
								((MessageViewMessages)msg).setMessages(null);
							else
							{
								((MessageViewMessages)msg).setMessages(new ArrayList<MessageDetails>());
								do{
									((MessageViewMessages)msg).getMessages().add(new MessageDetails(rs.getString(1),rs.getString(2)));
								} while (rs.next());
								client.sendToClient(msg);
							}
							rs.close();
						}
				/**retrieves tariffs of images from server*/
						else if (msg instanceof MessageRetrieveTariff){
							MessageRetrieveTariff messageRetrieveTariff = (MessageRetrieveTariff)msg;
							ResultSet rs = stmt.executeQuery(messageRetrieveTariff.getQuery());
							rs.next();
							
							TariffRow tariffRow = new TariffRow();
							
							tariffRow.setImage(rs.getFloat(2));
							tariffRow.setTimeLocation(rs.getFloat(3));
							tariffRow.setImagePack(rs.getFloat(4));
							tariffRow.setDataLayer(rs.getFloat(5));
							tariffRow.setStatus(rs.getInt(6));
							
							messageRetrieveTariff.setCurrentTariff(tariffRow);
							if(rs.next()){
								tariffRow = new  TariffRow();
								
								tariffRow.setImage(rs.getFloat(2));
								tariffRow.setTimeLocation(rs.getFloat(3));
								tariffRow.setImagePack(rs.getFloat(4));
								tariffRow.setDataLayer(rs.getFloat(5));
								messageRetrieveTariff.setPendingTariff(tariffRow);
								tariffRow.setStatus(rs.getInt(6));
							}
							
							
							client.sendToClient(messageRetrieveTariff);
							rs.close();
						}
				/**message for modifying tariffs in database*/
						else if(msg instanceof MessageChangeTariff){
							MessageChangeTariff messageChangeTariff = (MessageChangeTariff)msg;
							stmt.executeUpdate(messageChangeTariff.getQuery());
							
							if(messageChangeTariff.isRemovePendingQuery())
							{
								stmt.executeUpdate(messageChangeTariff.getAdditionUpdateQuery());
							}
						}
						else if(msg instanceof MessageApproveTariff){
							MessageApproveTariff messageApproveTariff = (MessageApproveTariff)msg;
							stmt.executeUpdate(messageApproveTariff.getQuery());
							
							/**send to manager client for online update*/
						}
				/**retrieves account history from database*/
						else if(msg instanceof MessageHistory)
						{
							MessageHistory messageHistory = (MessageHistory)msg;
					
							messageHistory.SearchHistory = new ArrayList<History>();
							messageHistory.PurchaseHistory = new ArrayList<History>();
					
							ResultSet rs = stmt.executeQuery(messageHistory.getQuery());
							if( rs.next() == false )
								messageHistory.SearchHistory=null;
							else do { messageHistory.SearchHistory.add(new History(rs.getString(1),rs.getString(2)));}
							while(rs.next());
							
							
							rs = stmt.executeQuery(messageHistory.getqPurchaseHistory());
							if( rs.next() == false )
								messageHistory.PurchaseHistory=null;
							else do { messageHistory.PurchaseHistory.add(new History(rs.getString(1),rs.getString(2)));}
							while(rs.next());
							
							client.sendToClient(messageHistory);
							
						}
				/**Receives image data from table (for removal)*/
						else if(msg instanceof MessageImageDelete)
						{
							ResultSet rs = stmt.executeQuery(((MessageImageDelete)msg).getQuery());
							if(rs.next() == false)
								((MessageImageDelete)msg).setimageDetails(null);
							else
							{
								((MessageImageDelete)msg).setimageDetails(new ArrayList<ImageDetails>());
								do{
									((MessageImageDelete)msg).getimageDetails().add(new ImageDetails(Integer.valueOf(rs.getString(1)),rs.getString(2),rs.getString(3)));
								} while (rs.next());
								client.sendToClient(msg);
							}
							rs.close();
						}
				/**removes image row from table*/
						else if(msg instanceof MessageImageRemoving)
						{
							stmt.executeUpdate(((MessageImageRemoving)msg).getQuery());
							serverUI.display("image number "+((MessageImageRemoving)msg).getimageid() + " removed.");
						}
						else if(msg instanceof MessageReviewImages)
						{
							ResultSet rs = stmt.executeQuery(((MessageReviewImages)msg).getQuery());
							if(rs.next() == false)
								((MessageReviewImages)msg).setimageDetails(null);
							else
							{
								((MessageReviewImages)msg).setimageDetails(new ArrayList<ImageDetails>());
								do{
									((MessageReviewImages)msg).getimageDetails().add(new ImageDetails(Integer.valueOf(rs.getString(1))));
								} while (rs.next());
								client.sendToClient(msg);
							}
							rs.close();
						}
				/**updates image details*/
						else if(msg instanceof MessageImageEdit)
						{
							Blob blob1 = new javax.sql.rowset.serial.SerialBlob(((MessageImageEdit)msg).getOriginalImage1());
							Blob blob2 = new javax.sql.rowset.serial.SerialBlob(((MessageImageEdit)msg).getOriginalImage2());
							Blob blob3 = new javax.sql.rowset.serial.SerialBlob(((MessageImageEdit)msg).getOriginalImage3());
							Blob blob4 = new javax.sql.rowset.serial.SerialBlob(((MessageImageEdit)msg).getOriginalImage4());
							Blob blob5 = new javax.sql.rowset.serial.SerialBlob(((MessageImageEdit)msg).getOriginalImage5());
							
							Connection conn = DriverManager.getConnection("jdbc:mysql://109.186.119.118/"+getdbName(), getUser(), getPass());
							String sql = "update images set date=?,location=?,latitude=?,longitude=?,sample=?,image1=?,image2=?,image3=?,image4=? where imageid= '"+(((MessageImageEdit)msg).getimageid()) +"' ";
							java.sql.PreparedStatement prepstmt = conn.prepareStatement(sql);
							prepstmt.setString(1, ((MessageImageEdit)msg).getDate());
							prepstmt.setString(2, ((MessageImageEdit)msg).getLocation());
							prepstmt.setString(3, ((MessageImageEdit)msg).getLatitude());
							prepstmt.setString(4, ((MessageImageEdit)msg).getLongitude());
							
						    prepstmt.setObject(5,((MessageImageEdit)msg).getOriginalImage1());
						    prepstmt.setObject(6,((MessageImageEdit)msg).getOriginalImage2());
						    prepstmt.setObject(7,((MessageImageEdit)msg).getOriginalImage3());
						    prepstmt.setObject(8,((MessageImageEdit)msg).getOriginalImage4());
						    prepstmt.setObject(9,((MessageImageEdit)msg).getOriginalImage5());
						    
						    prepstmt.execute();
						    							
							serverUI.display("Image has been edited");
						}
				/**retrieves image details*/
						else if(msg instanceof MessageGetImageEdit)
						{
							ResultSet rs = stmt.executeQuery(((MessageGetImageEdit)msg).getQuery());
							rs.next();
							Blob blob1 = rs.getBlob(6);
							Blob blob2 = rs.getBlob(7);
							Blob blob3 = rs.getBlob(8);
							Blob blob4 = rs.getBlob(9);
							Blob blob5 = rs.getBlob(10);
							
							((MessageGetImageEdit)msg).setimageDetails(new ArrayList<ImageFullDetails>());
							((MessageGetImageEdit)msg).getimageDetails().add(new ImageFullDetails(rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),blob1.getBytes(1,(int)blob1.length()), blob2.getBytes(1,(int)blob2.length()), blob3.getBytes(1,(int)blob3.length()), blob4.getBytes(1,(int)blob4.length()), blob5.getBytes(1,(int)blob5.length())));
							client.sendToClient(msg);
							rs.close();
						}
				/**aggregates data and creates requested monthly reports*/
						else if (msg instanceof MessageCreateReport) {
							if (((MessageCreateReport)msg).getReportType()==0) /**creates row for monthly complaints report*/
							{
								Date date = new Date();
								ResultSet rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900");
								rs.next();
								int totalAmount = Integer.valueOf(rs.getString(1));
								rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900 and pending=1");
								rs.next();
								int pending = Integer.valueOf(rs.getString(1));
								rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900 and approved=1");
								rs.next();
								int approved = Integer.valueOf(rs.getString(1));
								rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900 and approved=0");
								rs.next();
								int declined = Integer.valueOf(rs.getString(1));
								rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900 and type=1");
								rs.next();
								int type1 = Integer.valueOf(rs.getString(1));
								rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900 and type=2");
								rs.next();
								int type2 = Integer.valueOf(rs.getString(1));
								rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900 and type=3");
								rs.next();
								int type3 = Integer.valueOf(rs.getString(1));
								rs = stmt.executeQuery("select count(*) from complaint where MONTH(datetime)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(datetime)="+((MessageCreateReport)msg).getDate().getYear()+"+1900 and type=4");
								rs.next();
								int type4 = Integer.valueOf(rs.getString(1));
								
								rs = stmt.executeQuery("select Count(*) from monthly_complaint_status where date = '"+(((int)date.getYear()+1900))+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"'");
								rs.next();
								if (Integer.valueOf((rs.getString(1))) == 0) {
									stmt.executeUpdate("INSERT INTO `sgi`.`monthly_complaint_status`(`date`, `total_amount`, `pending`, `approved`, `declined`, `type_employee_behavior`, `type_insufficient_content`, `type_prices`, `type_other`) VALUES ('"+(((int)date.getYear()+1900))+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"', '"+totalAmount+"', '"+pending+"', '"+approved+"', '"+declined+"', '"+type1+"', '"+type2+"', '"+type3+"', '"+type4+"')");
								}//INSERT INTO `sgi`.`daily_statistics` (`Date`, `datetime_subs`, `datalayer_subs`, `imagepack_subs`, `purchased_images`, `purchased_samples`, `new_images`, `archive_size`) VALUES
								else
								{
									stmt.executeUpdate("update monthly_complaint_status set total_amount="+totalAmount+",pending="+pending+",approved="+approved+",declined="+declined+",type_employee_behavior="+type1+",type_insufficient_content="+type2+",type_prices="+type3+",type_other="+type4+" where date = '"+(((int)date.getYear()+1900))+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"'");
								}
								((MessageCreateReport)msg).reportCreated = true;
								client.sendToClient(msg);
								rs.close();
							}
							else if (((MessageCreateReport)msg).getReportType()==1) /**creates row for monthly database status report*/
							{
								Date date = new Date();
								ResultSet rs = stmt.executeQuery("select Count(imageid) from images");
								rs.next();
								int archiveSize = Integer.valueOf(rs.getString(1));
								
								rs = stmt.executeQuery("select sum(purchased_images) from daily_statistics where MONTH(date)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(date)="+((MessageCreateReport)msg).getDate().getYear()+"+1900");
								rs.next();
								int purchasedImages=0;
								if (!(rs.getString(1) == null))
									purchasedImages = Integer.valueOf(rs.getString(1));
								else
									purchasedImages = 0;
								
								rs = stmt.executeQuery("select sum(new_images) from daily_statistics where MONTH(date)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(date)="+((MessageCreateReport)msg).getDate().getYear()+"+1900");
								int newImages=0;
								rs.next();
								if (!(rs.getString(1) == null))
									newImages		= Integer.valueOf(rs.getString(1));
								else 
									newImages = 0;
								
								rs = stmt.executeQuery("select Count(*) from database_reports where MONTH(date)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(date)="+((MessageCreateReport)msg).getDate().getYear()+"+1900");
								rs.next();
								if (Integer.valueOf((rs.getString(1))) == 0) {
									stmt.executeUpdate("INSERT INTO database_reports VALUES ('"+(((int)date.getYear()+1900))+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"',"+archiveSize+","+newImages+","+purchasedImages+")");
								}
								else
								{
									stmt.executeUpdate("update database_reports set archive_size="+archiveSize+",new_images="+newImages+",purchased_images="+purchasedImages+" where MONTH(date)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(date)="+((MessageCreateReport)msg).getDate().getYear()+"+1900");
								}
								((MessageCreateReport)msg).reportCreated = true;
								client.sendToClient(msg);
								rs.close();
							}
							else if (((MessageCreateReport)msg).getReportType()==2) /**creates row for image pack usage report*/
							{
								Date date = new Date();
								ResultSet rs = stmt.executeQuery("select count(*) from date_time_subscription");
								rs.next();
								int datetimeSubs = Integer.valueOf(rs.getString(1));
								rs.close();
								rs = stmt.executeQuery("select count(*) from data_layer_subscription");
								rs.next();
								int datalayerSubs = Integer.valueOf(rs.getString(1));
								//rs.close();
								rs = stmt.executeQuery("select COUNT(*),sum(count) from image_pack_subscription");
								rs.next();
								int imagepackSubs = Integer.valueOf(rs.getString(1));
								int avgImagepackUsage = Integer.valueOf(rs.getString(2));
								avgImagepackUsage = (200*imagepackSubs-avgImagepackUsage)/imagepackSubs; /**how many images are used per pack on average*/
								rs.close();
								rs = stmt.executeQuery("select Count(*) from imagepack_sub_reports where MONTH(date)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(date)="+((MessageCreateReport)msg).getDate().getYear()+"+1900");
								rs.next();
								if (Integer.valueOf((rs.getString(1))) == 0) {
									stmt.executeUpdate("INSERT INTO imagepack_sub_reports VALUES ('"+(((int)date.getYear()+1900))+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"',"+datetimeSubs+","+datalayerSubs+","+imagepackSubs+","+avgImagepackUsage+")");
								}
								else
								{
									stmt.executeUpdate("update database_reports set datetime_subs="+datetimeSubs+",datalayer_subs="+datalayerSubs+",imagepack_subs="+imagepackSubs+",avg_imagepack_usage="+avgImagepackUsage+" where MONTH(date)="+((MessageCreateReport)msg).getDate().getMonth()+"+1 and YEAR(date)="+((MessageCreateReport)msg).getDate().getYear()+"+1900");
								}
								((MessageCreateReport)msg).reportCreated = true;
								client.sendToClient(msg);
								rs.close();
							}
						}
						else if(msg instanceof MessageRetrieveReport) 
						{
							ResultSet rs = stmt.executeQuery(((MessageRetrieveReport) msg).getQuery());
							CachedRowSetImpl temp1 = new CachedRowSetImpl();
							temp1.populate(rs);
							((MessageRetrieveReport) msg).setRS(temp1);
//							if (((MessageRetrieveReport) msg).getReportType() == 5) {
//								CachedRowSetImpl temp2 = new CachedRowSetImpl();
//								rs = stmt.executeQuery(((MessageRetrieveReport) msg).getType5Query());
//								temp2.populate(rs);
//								((MessageRetrieveReport) msg).setType5SubNum(1);
//								temp2.close();
//							}
							client.sendToClient(msg);
							rs.close();
							temp1.close();
							
						}
						/** retrieves tariff info */
						else if(msg instanceof MessageTariffExtract)
						{
							
							ResultSet rs = stmt.executeQuery(((MessageTariffExtract)msg).getQuery());
							rs.next();
							int id = rs.getInt(1);
							rs= stmt.executeQuery("select image,time_location_subscription,image_pack_subscription,data_layer_subscription from tariff where id="+id);
							rs.next();
							((MessageTariffExtract)msg).setTariffInfo(new ArrayList<TariffDetails>());
							((MessageTariffExtract)msg).getTariffInfo().add(new TariffDetails(String.valueOf(rs.getFloat(1)),String.valueOf(rs.getFloat(2)),String.valueOf(rs.getFloat(3)),String.valueOf(rs.getFloat(4))));
							client.sendToClient(msg);
							rs.close();
						}
						else
						{
							serverUI.display("Unknown type of message Received");
						}
				
			} catch (Exception e) {e.printStackTrace();}
	  }
	  
	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPass() {
		return pass;
	}
	
	public void setPass(String pass) {
		this.pass = pass;
	}

	public void setdbName(String dbName) {
		this.dbName = dbName;
	}
	public String getdbName() {
		return dbName;
	}
	
	/**updates daily_statistics table*/
	@SuppressWarnings("deprecation")
	private void UpdateDailyStats(Statement stmt, String message) throws SQLException { //Updates daily_stats table
		
		Date date = new Date();
		ResultSet rs = stmt.executeQuery("select Count(*) from daily_statistics where Date = '"+(((int)date.getYear()+1900))+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"'");
		rs.next();
		if (Integer.valueOf((rs.getString(1))) == 0) {
			rs = stmt.executeQuery("select Count(imageid) from images");
			rs.next();
			stmt.executeUpdate("INSERT INTO `sgi`.`daily_statistics` (`Date`, `datetime_subs`, `datalayer_subs`, `imagepack_subs`, `purchased_images`, `purchased_samples`, `new_images`, `archive_size`) VALUES ('"+((int)date.getYear()+1900)+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"', '0', '0', '0', '0', '0', '0', '"+Integer.valueOf(rs.getString(1))+"')");
		}
		String q = "update daily_statistics set "+message+" where Date = '"+(((int)date.getYear()+1900))+"-"+((int)date.getMonth()+1)+"-"+((int)date.getDate())+"'";
		stmt.executeUpdate(q);
	}
	
}
